package com.qf.index;

import cn.hutool.core.collection.ConcurrentHashSet;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import com.alibaba.fastjson.JSONObject;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

public class ZkyOneQuArticle {

    /**
     * 中科院近6年一区的PubMed论文，以及在服务器上的全文地址清单。共计1,347,105条，2025年1月20日
     * 贾新志
     */

    public static void main(String[] args) throws SQLException {

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        System.out.println(format.format(new Date()) + " 程序开始了~~~");

        String pmArticleTable = "scholar_25_01.spider_pubmed_vip_extend_0114";
        String jourMergeTable = "scholar_25_01.basic_jour_merge";
        String fullPathTable = "dir.ncbijournalpdf_20250116_fullpath";
        String zky12019PmIdTable = "dir.zky_1_2019_pmid";

        Set<String> nlmIdSet = getNlmIdSet(jourMergeTable, "id", "nc_nlmid_list`,`zky_dalei");
        System.out.println("nlmIdSet.size():" + nlmIdSet.size());
        Set<String> pmIdSet = getPmIdSet(pmArticleTable, "id", "pub_year`,`med_nlm_id`,`pmid", nlmIdSet);
        System.out.println("pmIdSet.size():" + pmIdSet.size());
        insertFullPathTable(fullPathTable, "pid", "fullpath", pmIdSet, zky12019PmIdTable);

    }

    public static void insertFullPathTable(String fullPathTable, String autoId, String field, Set<String> pmIdSet, String zky12019PmIdTable) throws SQLException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        int onePageNum = 1000;
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + fullPathTable);
        Set<String> set = new ConcurrentHashSet<>();
        Db.use().execute("truncate table " + zky12019PmIdTable);
        if (tableNum.get(0).getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + fullPathTable).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            tableList.parallelStream().forEach(startId -> {
                try {
                    List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + fullPathTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                    System.out.println("\033[31;1m" + format.format(new Date()) + " 读表现在开始：" + startId + " 表名：" + fullPathTable + Utils.printMemoryStr() + "\033[0m");
                    for (Entity entry : tableData) {
                        String fullpath = entry.getStr("fullpath");
                        String fileId = Utils.getFileId(fullpath);
                        if (pmIdSet.contains(fileId) && !set.contains(fileId)) {
                            try {
                                Db.use().insert(Entity.create(zky12019PmIdTable).set("pmId", fileId.substring(2)).set("fullpath", fullpath));
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                            set.add(fileId);
                        }
                    }
                } catch (SQLException e) {
//                    throw new RuntimeException(e);
                }
            });
        }
    }

    public static Set<String> getPmIdSet(String pmArticleTable, String autoId, String field, Set<String> nlmIdSet) throws SQLException {
        // 获取2018年以来的近6年的，中科院一区的论文清单
        int onePageNum = 1000;
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + pmArticleTable);
        Set<String> pmIdSet = new HashSet<>();
        if (tableNum.get(0).getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + pmArticleTable).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            tableList.parallelStream().forEach(startId -> {
                try {
                    List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + pmArticleTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                    for (Entity entry : tableData) {
                        int pubYear = Utils.getIntField(entry, "pub_year");
                        if (nlmIdSet.contains(entry.getStr("med_nlm_id")) && pubYear > 2018) {
                            pmIdSet.add("PM" + Utils.getIntField(entry, "pmid"));
                        }
                    }
                } catch (SQLException e) {
//                    throw new RuntimeException(e);
                }
            });
        }
        return pmIdSet;
    }


    public static Set<String> getNlmIdSet(String jourMergeTable, String autoId, String field) throws SQLException {
        // 获取中科院一区的期刊清单
        int onePageNum = 1000;
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + jourMergeTable);
        Set<String> nlmIdSet = new HashSet<>();
        if (tableNum.get(0).getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + jourMergeTable).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + jourMergeTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                for (Entity entry : tableData) {
                    String ncNlmids = entry.getStr("nc_nlmid_list");
                    if ("1".equals(entry.getStr("zky_dalei")) && null != ncNlmids && ncNlmids.contains("\"]")) {
                        List<String> ncNlmidList = JSONObject.parseArray(ncNlmids, String.class);
                        for (String nlmId : ncNlmidList) {
                            nlmIdSet.add(nlmId.toUpperCase().trim());
                        }
                    }
                }
            }
        }
        return nlmIdSet;
    }


}
